﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Text.RegularExpressions;

namespace Maspalomas
{
   public partial class ReportingGrid : Form
   {
      public OdbcDSConnection Conn {get; set;}
      protected Stack<string> m_OperationStack = new Stack<string>();
      protected bool showFind = false;
      
      public ReportingGrid()
      {
         InitializeComponent();

         foreach (ReportingStatement repoStatement in ReportingStatements.Instance.Statements)
            cmbStatement.Items.Add(repoStatement.Name);
         if (cmbStatement.Items.Count > 0)
            cmbStatement.SelectedIndex = 0;
         txtYear.Text = DateTime.Now.Year.ToString();
      }

      public ReportingGrid(bool showFind) : this()
      {
         if(showFind)
            this.showFind = showFind;
      }
      
      void ReportingGrid_Shown(object sender, EventArgs e)
      {
         toolStripStatusLabel1.Text = string.Empty;

         if(showFind)
            ShowFind();
      }

      private void btnExecute_Click(object sender, EventArgs e)
      {
         try
         {
            if (Conn == null)
               return;

            if(Conn.WorkOffline)
            {
               MessageBox.Show("Cannot execute statements in offline mode");
               return;
            }

            UpdateStatementWithAdditionalCondition();
            
            string statement = txtStatement.Text;
            DataSet ds = SqlOdbcExecution.ExecuteSelect(statement, Conn.Connection);
            /*grdResults.Columns.Clear();
            foreach (DataColumn col in ds.Tables[0].Columns)
               grdResults.Columns.Add("col", col.Caption);
            grdResults.Rows.Clear();
            foreach (DataRow dr in ds.Tables[0].Rows)
               grdResults.Rows.Add(dr.ItemArray);*/

            grdResults.DataSource = ds;
            grdResults.DataMember = ds.Tables[0].TableName;
            for (int i = 0; i < grdResults.Columns.Count; i++)
               grdResults.Columns[i].HeaderText = ds.Tables[0].Columns[i].Caption;
            grdResults.Focus();
            m_OperationStack.Push(statement);
         }
         catch (Exception ex)
         {
            MessageBox.Show(ex.Message, "Statement exectution error", MessageBoxButtons.OK, MessageBoxIcon.Error);
         }
      }

      private void UpdateStatementWithAdditionalCondition()
      {
         if (!string.IsNullOrEmpty(txtAdditionalCondition.Text))
            txtStatement.Text = txtStatement.Text.Replace("WHERE ", string.Format("WHERE {0} AND ", txtAdditionalCondition.Text));
      }

      private void ReportingGrid_KeyDown(object sender, KeyEventArgs e)
      {
         if (e.KeyCode == Keys.Escape)
            Close();
         else if (e.KeyCode == Keys.Enter)
            grdResults_DoubleClick(null, null);
         else if (e.KeyCode == Keys.Back && grdResults.Focused)
            ExecutePreviousOperation();
         else if (e.KeyCode == Keys.F3)
            GroupCurrentStatement();
         else if (e.KeyCode == Keys.F12)
            ExportToXml();
         else if (e.KeyCode == Keys.F4)
         {
            ExecuteStatementByName(ReportingStatements.c_ThisMonth);
            e.SuppressKeyPress = true;
         }
         else if (e.KeyCode == Keys.F5)
            ExecuteStatementByName(ReportingStatements.c_PrevMonth);
         else if (e.KeyCode == Keys.F6)
            ExecuteStatementByName(ReportingStatements.c_ThisYearMonthByMonth);
         else if (e.KeyCode == Keys.F7)
            ExecuteStatementByName(ReportingStatements.c_ThisYearMonthByMonthCat);
         else if (e.KeyCode == Keys.F8)
         {
            if (txtYear.Text == DateTime.Now.Year.ToString())
               ExecuteStatementByName(ReportingStatements.c_PivotAnnual);
            else
               ExecuteStatement(ReportingStatements.GetStatement_PivotAnnual(int.Parse(txtYear.Text)), ReportingStatements.c_PivotAnnual);
         }
         else if (e.Control && e.KeyCode == Keys.F)
            ShowFind();

         //e.SuppressKeyPress = true;
      }

      private void ExecuteStatementByName(string statementName)
      {
         if (statementName == string.Empty)
            return;
         cmbStatement.Text = statementName;
         btnExecute_Click(null, null);
      }

      private void ExecuteStatement(string statement, string statementName)
      {
         cmbStatement.Text = statementName;
         if (statement == string.Empty)
            return;
         txtStatement.Text = statement;
         btnExecute_Click(null, null);
      }
      
      private void cmbStatement_SelectedValueChanged(object sender, EventArgs e)
      {
         foreach (ReportingStatement repoStatement in ReportingStatements.Instance.Statements)
            if (repoStatement.Name == cmbStatement.Text)
            {
               txtStatement.Text = repoStatement.Statement;
               break;
            }
      }

      private void grdResults_SelectionChanged(object sender, EventArgs e)
      {
         toolStripStatusLabel1.Text = string.Empty;

         //if (grdResults.SelectedCells.Count < 2)
         //   return;

         double sum = 0;
         double avg = 0;
         int i = 0;
         foreach(DataGridViewCell cell in grdResults.SelectedCells)
         {
            double val;
            if (double.TryParse(cell.Value.ToString().Replace(".", ","), out val))
            {
               sum += val;
               i++;
            }
         }
         if (i > 0)
            avg = Math.Round(sum / i, 2);
         toolStripStatusLabel1.Text = string.Format("SUM: {0}; AVG: {1}", sum.ToString(), avg.ToString());
      }

      private string CreateWhereClause(List<string> clauses)
      {
         if(clauses.Count == 0)
            return string.Empty;

         string retVal = " WHERE " + clauses[0];

         for (int i = 1; i < clauses.Count; i++)
            retVal += " AND " + clauses[i];

         return retVal;
      }

      private void grdResults_DoubleClick(object sender, EventArgs e)
      {
         if (grdResults.SelectedCells.Count < 1)
            return;

         List<string> clauses = GetSelectedClauses();
         List<string> dateClauses = GetDateClause(txtStatement.Text);
         if (dateClauses.Count == 0)
         {
            if (!CreateWhereClause(clauses).Contains("date"))
               clauses.Add(ReportingStatements.CurrentMonthClause);
         }
         else
            clauses.AddRange(dateClauses);

         txtStatement.Text = ReportingStatements.GetStatement_All(CreateWhereClause(clauses));
         btnExecute_Click(null, null);
      }

      protected List<string> GetDateClause(string statement)
      {
         List<string> clauses = new List<string>();

         string[] patterns =
         {
            "date( )*[<=>]+( )*('\\d{4}-\\d{2}-\\d{2}')",
            @"date_part\(['a-z, ]+\)( )*=( )*\d{1,4}"
         };

         foreach(string sPattern in patterns)
            if (Regex.IsMatch(statement, sPattern))
            {
               MatchCollection matches = Regex.Matches(statement, sPattern);
               foreach (Match m in matches)
                  if(!clauses.Contains(m.Value))
                     clauses.Add(m.Value);
            }

         return clauses;
      }

      protected List<string> GetSelectedClauses()
      {
         List<string> clauses = new List<string>();

         foreach (DataGridViewCell selectedCell in grdResults.SelectedCells)
         {
            string columnClause;
            string clickedCol = selectedCell.OwningColumn.HeaderText;
            if (clickedCol == "year" || clickedCol == "month")
            {
               clickedCol = string.Format("date_part('{0}', date)", clickedCol);
               columnClause = string.Format("{0}={1}", clickedCol, selectedCell.Value.ToString());
            }
            else if (cmbStatement.Text == ReportingStatements.c_PivotAnnual)
            {
               string rowCategory = grdResults.Rows[selectedCell.RowIndex].Cells[0].Value.ToString();
               columnClause = string.Format("category='{0}' AND date_part('month', date)={1}", rowCategory, selectedCell.ColumnIndex.ToString());
            }
            else
               columnClause = string.Format("{0}='{1}'", clickedCol, selectedCell.Value.ToString());

            clauses.Add(columnClause);
         }

         return clauses;
      }

      private void ExecutePreviousOperation()
      {
         if (m_OperationStack.Count > 1)
         {
            m_OperationStack.Pop();
            txtStatement.Text = m_OperationStack.Pop();
            btnExecute_Click(null, null);
         }
      }

      private void GroupCurrentStatement()
      {
         string whereClause = CreateWhereClause(GetDateClause(txtStatement.Text));
         txtStatement.Text = ReportingStatements.GetStatement_MonthGroupedByCat(whereClause);
         btnExecute_Click(null, null);
      }

      private void ExportToXml()
      {
         DataSet ds = (DataSet)grdResults.DataSource;
         ds.Tables[0].TableName = "Expenses";
         ds.Tables[0].Columns[0].ColumnName = "Payment";
         ds.Tables[0].Columns[1].ColumnName = "Date";
         ds.Tables[0].Columns[2].ColumnName = "Amount";
         ds.Tables[0].Columns[3].ColumnName = "Category";
         ds.Tables[0].Columns[4].ColumnName = "SubCategory";
         ds.Tables[0].Columns[5].ColumnName = "Comment1";
         ds.Tables[0].Columns[6].ColumnName = "Comment2";

         ds.WriteXml("exportedData.xml");
         MessageBox.Show("Data sucessfully exported", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
      }

      private void ShowFind()
      {
         Find dlgFind = new Find();
         if (dlgFind.ShowDialog(this) == DialogResult.OK)
            ExecuteStatement(ReportingStatements.GetStatement_FindText(dlgFind.SearchFor, dlgFind.UseLike, dlgFind.CaseSensitive), string.Empty);
      }
   }
}
